﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Interfaces;
using System.Data.SQLite;
using System.ComponentModel;
using BusinessObjects;
using System.Data;
using System.Globalization;
using System.Drawing;
using Toolkit;
using CryptoUtils;
using Common;

namespace DataAccess {
  /// <summary>
  /// Data Access Layer for user
  /// </summary>
  public class UserDal : IUserLogic {

    #region - Public methods -

    /// <summary>
    /// Gets the user.
    /// </summary>
    /// <param name="loginName">Name of the login.</param>
    /// <returns></returns>
    public User GetUser( string loginName ) {
      User user = new User( );
      using ( SQLiteConnection connection = DataHelper.CreateConnection( ) ) {
        using ( SQLiteCommand command = CreateSelectUserCommand( connection, loginName ) ) {
          try {
            using ( SQLiteDataReader reader = command.ExecuteReader( CommandBehavior.CloseConnection ) ) {
              while ( reader.Read( ) ) {

                FillUser( user, reader );

              }
            }
          } catch ( Exception ex ) {
            throw new DataException( ex.Message, ex );
          } finally {
            if ( connection.State != ConnectionState.Closed ) {
              connection.Close( );
            }
          }
        }
      }
      return user;
    }

    /// <summary>
    /// Gets the auto logon user.
    /// </summary>
    /// <returns></returns>
    public User GetAutoLogonUser( ) {
      User user = null;
      using ( SQLiteConnection connection = DataHelper.CreateConnection( ) ) {
        using ( SQLiteCommand command = CreateSelectForAutoLogonCommand( connection ) ) {
          try {
            using ( SQLiteDataReader reader = command.ExecuteReader( CommandBehavior.CloseConnection ) ) {
              while ( reader.Read( ) ) {
                user = new User( );
                FillUser( user, reader );
              }
            }
          } catch ( Exception ex ) {
            throw new DataException( ex.Message, ex );
          } finally {
            if ( connection.State != ConnectionState.Closed ) {
              connection.Close( );
            }
          }
        }
      }
      return user;
    }

    /// <summary>
    /// Saves the user.
    /// </summary>
    /// <param name="user">The user.</param>
    public void SaveUser( User user ) {
      if ( user.Id == 0 ) {
        InsertUser( user );
      } else {
        UpdateUser( user );
      }
    }

    /// <summary>
    /// Saves the user password.
    /// </summary>
    /// <param name="user">The user.</param>
    public void SaveUserPassword( User user ) {
      using ( SQLiteConnection connection = DataHelper.CreateConnection( ) ) {
        using ( SQLiteTransaction transaction = connection.BeginTransaction( ) ) {
          SQLiteCommand command = CreateUpdatePasswordCommand( connection, user );
          command.Transaction = transaction;
          try {
            command.ExecuteNonQuery( );
            transaction.Commit( );
          } catch ( Exception ex ) {
            transaction.Rollback( );
            throw new DataException( ex.Message, ex );
          } finally {
            SettingsSingleton.Settings.CurrentUser = GetUser( user.LoginName );
            if ( connection.State != ConnectionState.Closed ) {
              connection.Close( );
            }
          }
        }
      }
    }

    #endregion

    #region - Private methods -
    
    private static void FillUser( User user, SQLiteDataReader reader ) {
      user.Id = reader.GetInt32( reader.GetOrdinal( "UserId" ) );
      user.Name = reader.GetValue( reader.GetOrdinal( "Name" ) ).ToString( );
      user.FirstName = reader.GetValue( reader.GetOrdinal( "FirstName" ) ).ToString( );
      user.LoginName = reader.GetValue( reader.GetOrdinal( "LoginName" ) ).ToString( );
      user.Password = reader.GetValue( reader.GetOrdinal( "Password" ) ).ToString( );
      user.Salt = reader.GetValue( reader.GetOrdinal( "Salt" ) ).ToString( );
      user.AutoLogin = reader.GetValue( reader.GetOrdinal( "AutoLogin" ) ) == DBNull.Value ? false : Convert.ToBoolean( reader.GetValue( reader.GetOrdinal( "AutoLogin" ) ), CultureInfo.CurrentCulture );
      user.Level = reader.GetValue( reader.GetOrdinal( "Level" ) ) == DBNull.Value ? ( byte ) 0 : Convert.ToByte( reader.GetValue( reader.GetOrdinal( "Level" ) ) );
      user.Email = reader.GetValue( reader.GetOrdinal( "UserEmail" ) ).ToString( );
      user.Phone = reader.GetValue( reader.GetOrdinal( "UserPhone" ) ).ToString( );
      user.EmergencyPhone = reader.GetValue( reader.GetOrdinal( "UserEmergencyPhone" ) ).ToString( );
      user.Gsm = reader.GetValue( reader.GetOrdinal( "UserGsm" ) ).ToString( );
      user.Address.City = reader.GetValue( reader.GetOrdinal( "UserCity" ) ).ToString( );
      user.Address.Zipcode = reader.GetValue( reader.GetOrdinal( "UserZipcode" ) ).ToString( );
      user.Address.ZipcodeId = reader.GetValue( reader.GetOrdinal( "UserZipcodeId" ) ) == DBNull.Value ? 0 : Convert.ToInt32( reader.GetValue( reader.GetOrdinal( "UserZipcodeId" ) ), CultureInfo.CurrentCulture );
      user.Address.Street = reader.GetValue( reader.GetOrdinal( "UserStreet" ) ).ToString( );
      user.Address.CountryCode = reader.GetValue( reader.GetOrdinal( "UserCountryCode" ) ).ToString( );
      user.Address.CountryId = reader.GetValue( reader.GetOrdinal( "UserCountryId" ) ) == DBNull.Value ? 0 : Convert.ToInt32( reader.GetValue( reader.GetOrdinal( "UserCountryId" ) ), CultureInfo.CurrentCulture ); ;
      user.Address.CountryName = reader.GetValue( reader.GetOrdinal( "UserCountryName" ) ).ToString( );
      if ( reader.GetValue( reader.GetOrdinal( "Photo" ) ) != DBNull.Value ) {
        byte[ ] photo = ( byte[ ] ) reader.GetValue( reader.GetOrdinal( "Photo" ) );
        Image image = GraphicTools.Convert.ByteArrayToImage( photo );
        if ( image != null ) {
          user.Photo = image;
        }
      }
      user.StartTime = reader.GetValue( reader.GetOrdinal( "UserStartTime" ) ).ToString( );
      user.StopTime = reader.GetValue( reader.GetOrdinal( "UserStopTime" ) ).ToString( );
    }

    private void InsertUser( User user ) {
      using ( SQLiteConnection connection = DataHelper.CreateConnection( ) ) {
        using ( SQLiteTransaction transaction = connection.BeginTransaction( ) ) {
          SQLiteCommand command = CreateInsertCommand( connection, user );
          command.Transaction = transaction;
          try {
            int val = command.ExecuteNonQuery( );
            command = DataHelper.CreateSelectAfterInsertCommand( connection, "Users" );
            user.Id = Convert.ToInt32( command.ExecuteScalar( ) );
            transaction.Commit( );
          } catch ( Exception ex ) {
            transaction.Rollback( );
            throw new DataException( ex.Message, ex );
          } finally {
            if ( connection.State != ConnectionState.Closed ) {
              connection.Close( );
            }
          }
        }
      }
    }

    private void UpdateUser( User user ) {
      using ( SQLiteConnection connection = DataHelper.CreateConnection( ) ) {
        using ( SQLiteTransaction transaction = connection.BeginTransaction( ) ) {
          SQLiteCommand command = CreateUpdateCommand( connection, user );
          command.Transaction = transaction;
          try {
            command.ExecuteNonQuery( );
            transaction.Commit( );
          } catch ( Exception ex ) {
            transaction.Rollback( );
            throw new DataException( ex.Message, ex );
          } finally {
            if ( connection.State != ConnectionState.Closed ) {
              connection.Close( );
            }
          }
        }
      }
    }

    #region - SQL statements -

    private SQLiteCommand CreateSelectUserCommand( SQLiteConnection connection, string loginName ) {
      SQLiteCommand command = connection.CreateCommand( );
      command.CommandText = BasicSelect( ) + " AND LoweredLoginName = @loginname";
      command.Parameters.AddWithValue( "@loginname", loginName.ToLower( CultureInfo.CurrentCulture ) );
      return command;
    }

    private SQLiteCommand CreateSelectForAutoLogonCommand( SQLiteConnection connection ) {
      SQLiteCommand command = connection.CreateCommand( );
      command.CommandText = BasicSelect( ) + " AND AutoLogin = 1";
      return command;
    }

    private SQLiteCommand CreateInsertCommand( SQLiteConnection connection, User user ) {
      SQLiteCommand command = connection.CreateCommand( );
      command.CommandText = @"INSERT INTO Users (Name, FirstName, 
                                LoginName, LoweredLoginName, AutoLogin,
                                LastActivity, Level, Email, Phone, 
                                EmergencyPhone, GSM, StartTime, StopTime,
                                Street, ZipcodeId, Photo, Password, Salt)
                              VALUES (@name, @firstname, @loginname, @loweredloginname,
                                @autologin, @lastactivity, @level, @email, @phone,
                                @emergencyphone, @gsm, @starttime, @stoptime,
                                @street, @zipcodeid, @photo, @password, @salt)";
      command.Parameters.AddWithValue( "@name", user.Name );
      command.Parameters.AddWithValue( "@firstname", user.FirstName );
      command.Parameters.AddWithValue( "@loginname", user.LoginName );
      command.Parameters.AddWithValue( "@loweredloginname", user.LoweredLoginName );
      command.Parameters.AddWithValue( "@autologin", user.AutoLogin );
      command.Parameters.AddWithValue( "@lastactivity", user.LastActivity );
      command.Parameters.AddWithValue( "@level", user.Level );
      command.Parameters.AddWithValue( "@email", user.Email );
      command.Parameters.AddWithValue( "@phone", user.Phone );
      command.Parameters.AddWithValue( "@emergencyphone", user.EmergencyPhone );
      command.Parameters.AddWithValue( "@gsm", user.Gsm );
      command.Parameters.AddWithValue( "@starttime", user.StartTime );
      command.Parameters.AddWithValue( "@stoptime", user.StopTime );
      command.Parameters.AddWithValue( "@street", user.Address.Street );
      command.Parameters.AddWithValue( "@zipcodeid", user.Address.ZipcodeId );
      byte[ ] photo = null;
      if ( user.Photo != null ) {
        photo = GraphicTools.Convert.ImageToByteArray( user.Photo, user.Photo.RawFormat );
      }
      command.Parameters.AddWithValue( "@photo", photo );
      string salt = string.Empty;
      string pass = string.Empty;

      pass = Cryptography.EncryptPassword( user.Password, out salt );

      command.Parameters.AddWithValue( "@password", pass );
      command.Parameters.AddWithValue( "@salt", salt );

      return command;
    }

    private SQLiteCommand CreateUpdateCommand( SQLiteConnection connection, User user ) {
      SQLiteCommand command = connection.CreateCommand( );
      command.CommandText = @"UPDATE Users SET Name = @name,
                              FirstName = @firstname, LoginName = @loginname,
                              LoweredLoginName = @loweredloginname, AutoLogin = @autologin,
                              LastActivity = @lastactivity, Level = @level,
                              Email = @email, Phone = @phone, GSM = @gsm,
                              EmergencyPhone = @emergencyphone, StartTime = @starttime,
                              StopTime = @stoptime, Street = @street, ZipcodeId = @zipcodeid,
                              Photo = @photo
                              WHERE ID = @id";
      command.Parameters.AddWithValue( "@name", user.Name );
      command.Parameters.AddWithValue( "@firstname", user.FirstName );
      command.Parameters.AddWithValue( "@loginname", user.LoginName );
      command.Parameters.AddWithValue( "@loweredloginname", user.LoweredLoginName );
      command.Parameters.AddWithValue( "@autologin", user.AutoLogin );
      command.Parameters.AddWithValue( "@lastactivity", user.LastActivity );
      command.Parameters.AddWithValue( "@level", user.Level );
      command.Parameters.AddWithValue( "@email", user.Email );
      command.Parameters.AddWithValue( "@phone", user.Phone );
      command.Parameters.AddWithValue( "@emergencyphone", user.EmergencyPhone );
      command.Parameters.AddWithValue( "@gsm", user.Gsm );
      command.Parameters.AddWithValue( "@starttime", user.StartTime );
      command.Parameters.AddWithValue( "@stoptime", user.StopTime );
      command.Parameters.AddWithValue( "@street", user.Address.Street );
      command.Parameters.AddWithValue( "@zipcodeid", user.Address.ZipcodeId );
      byte[ ] photo = null;
      if ( user.Photo != null ) {
        photo = GraphicTools.Convert.ImageToByteArray( user.Photo, user.Photo.RawFormat );
      }
      command.Parameters.AddWithValue( "@photo", photo );
      command.Parameters.AddWithValue( "@id", user.Id );
      return command;
    }

    private SQLiteCommand CreateUpdatePasswordCommand( SQLiteConnection connection, User user ) {
      SQLiteCommand command = connection.CreateCommand( );
      command.CommandText = @"UPDATE Users SET Name = @name,
                              FirstName = @firstname, LoginName = @loginname,
                              LoweredLoginName = @loweredloginname, AutoLogin = @autologin,
                              LastActivity = @lastactivity, Level = @level,
                              Email = @email, Phone = @phone, GSM = @gsm,
                              EmergencyPhone = @emergencyphone, StartTime = @starttime,
                              StopTime = @stoptime, Street = @street, ZipcodeId = @zipcodeid,
                              Photo = @photo, Password = @password, Salt = @salt
                              WHERE ID = @id";
      command.Parameters.AddWithValue( "@name", user.Name );
      command.Parameters.AddWithValue( "@firstname", user.FirstName );
      command.Parameters.AddWithValue( "@loginname", user.LoginName );
      command.Parameters.AddWithValue( "@loweredloginname", user.LoweredLoginName );
      command.Parameters.AddWithValue( "@autologin", user.AutoLogin );
      command.Parameters.AddWithValue( "@lastactivity", user.LastActivity );
      command.Parameters.AddWithValue( "@level", user.Level );
      command.Parameters.AddWithValue( "@email", user.Email );
      command.Parameters.AddWithValue( "@phone", user.Phone );
      command.Parameters.AddWithValue( "@emergencyphone", user.EmergencyPhone );
      command.Parameters.AddWithValue( "@gsm", user.Gsm );
      command.Parameters.AddWithValue( "@starttime", user.StartTime );
      command.Parameters.AddWithValue( "@stoptime", user.StopTime );
      command.Parameters.AddWithValue( "@street", user.Address.Street );
      command.Parameters.AddWithValue( "@zipcodeid", user.Address.ZipcodeId );
      byte[ ] photo = null;
      if ( user.Photo != null ) {
        photo = GraphicTools.Convert.ImageToByteArray( user.Photo, user.Photo.RawFormat );
      }
      command.Parameters.AddWithValue( "@photo", photo );
      command.Parameters.AddWithValue( "@id", user.Id );
      string salt = string.Empty;
      string pass = string.Empty;

      pass = Cryptography.EncryptPassword( user.Password, out salt );

      command.Parameters.AddWithValue( "@password", pass );
      command.Parameters.AddWithValue( "@salt", salt );
      return command;
    }

    private string BasicSelect( ) {
      return @"SELECT Users.ID AS UserId, Users.Name,
                               Users.FirstName, Users.LoginName, Users.Photo, Users.Password, Users.Salt,
                               Users.AutoLogin, Users.LastActivity, Users.Level, Users.Email AS UserEmail,
                               Users.Phone AS UserPhone, Users.EmergencyPhone AS UserEmergencyPhone,
                               Users.GSM AS UserGsm, Users.StartTime AS UserStartTime, Users.StopTime AS UserStopTime,
                               Users.Street AS UserStreet, ZipCodes.ID AS UserZipcodeId, ZipCodes.Zipcode AS UserZipcode,
                               ZipCodes.City AS UserCity, Countries.Country AS UserCountryName,
                               Countries.CountryCode AS UserCountryCode, Countries.ID AS UserCountryId
                              FROM Users, ZipCodes,Countries
                              WHERE ZipCodes.[CountryId] = Countries.[ID]";
    }

    #endregion

    #endregion
  }
}
